package com.itheima.dao;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.itheima.bean.Product;
import com.itheima.utils.DataSourceUtils;

public class ProductDao {

	/**
	 * 查询所有商品
	 * @return
	 * @throws SQLException 
	 */
	public List<Product> findAll() throws SQLException {
		//创建QueryRunner
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		//编写sql
		String sql = "select * from product";
		//执行sql
		List<Product> query = qr.query(sql, new BeanListHandler<Product>(Product.class));
		return query;
	}

	/*
	 *添加商品 
	 */
	public void saveProduct(Product pro) throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		
		String sql = "insert into product values(?,?,?,?,?,?,?)";
		
		qr.update(sql, pro.getPid(),pro.getPname(),pro.getMarket_price(),pro.getShop_price(),pro.getPimage(),pro.getPdate(),pro.getPdesc());
	}
	/**
	 * 根据id查询数据
	 * @param id
	 * @return
	 * @throws SQLException 
	 */
	public Product getProByPid(String id) throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		
		String sql = "select * from product where pid=?";
		
		Product query = qr.query(sql, new BeanHandler<Product>(Product.class), id);
		return query;
	}
	/**
	 * 根据id修改商品
	 * @param pro
	 * @throws SQLException 
	 */
	public void updatePro(Product pro) throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		
		String sql = "update product set pname=?,market_price=?,shop_price=?,pdesc=? where pid=?";
		
		qr.update(sql, pro.getPname(),pro.getMarket_price(),pro.getShop_price(),pro.getPdesc(),pro.getPid());
	}

	/**
	 * 根据id删除商品信息
	 * @param pid
	 * @throws SQLException 
	 */
	public void deletePro(String pid) throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		
		String sql = "delete from product where pid = ?";
		
		qr.update(sql, pid);
	}

}
